package com.yzyz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.yzyz.bean.route.RouteBean;
import com.yzyz.dao.RouteDao;
import com.yzyz.utils.ValidateUtils;
import com.yzyz.utils.mapper.RouteRowMapper;

/**
 * 
 * @author David Sun
 * @Date   2010-3-14
 *
 */
public class RouteDaoImpl implements RouteDao {
	private JdbcTemplate jdbcTemplate;
	@SuppressWarnings("unchecked")
	@Override
	public RouteBean get(final String name) {
		String sql = "select * from route where name=?";
		List<RouteBean> list = this.jdbcTemplate.query(sql, new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, name);
			}
		}, new RouteRowMapper());
		if(list == null) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public int delete(int id) {
		String sql = "delete from route where id=" + id;
		return this.jdbcTemplate.update(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Object get(int id) {
		String sql = "select * from route where id=" + id;
		List<RouteBean> list = this.jdbcTemplate.query(sql, new RouteRowMapper());
		return list == null ? null:list.size()==0?null:list.get(0);
	}

	@Override
	public int save(Object object) {
		final RouteBean route = (RouteBean)object;
		final String sql = "insert into route (contents,date,price,name,level, routeGroupId) values (?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
			public java.sql.PreparedStatement createPreparedStatement(
					Connection conn) throws SQLException {
				java.sql.PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, route.getContents());
				ps.setTimestamp(2, new java.sql.Timestamp(route.getDate().getTime()));
				ps.setString(3, route.getPrice());
				ps.setString(4, route.getName());
				ps.setInt(5, route.getLevel());
				ps.setInt(6, route.getRouteGroupId());
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();

	}

	@Override
	public void update(Object object) {
		final RouteBean route = (RouteBean)object;
		String sql = "update route set contents=?, name=?, price=?,level=?, routeGroupId=? where id=?";
		this.jdbcTemplate.update(sql, new Object[]{route.getContents(), route.getName(), route.getPrice(), route.getLevel(),route.getRouteGroupId() ,route.getId()});
	}

	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		
	}

	@Override
	public int updateAvailable(int id, boolean available) {
		String sql = "update route set available=? where id=?";
		return this.jdbcTemplate.update(sql, new Object[]{available, id});
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RouteBean> getAllWithoutDisabled() {
		String sql = "select * from route where available=1";
		return this.jdbcTemplate.query(sql, new RouteRowMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RouteBean> getAllWithoutDisabled(int routeGroupId) {
		String sql = "select * from route where routeGroupId=" + routeGroupId + " and available=1 order by level desc";
		return this.jdbcTemplate.query(sql, new RouteRowMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RouteBean> getTopN(int start, int limit) {
		String sql = "select * from route where available=1 order by level desc limit " + start + ", " + limit;
		return this.jdbcTemplate.query(sql, new RouteRowMapper());
	}

	@Override
	public int getCountOfAll(String routeName, int groupId) {
		String sql = "select count(*) from route where 1=1";
		if(!ValidateUtils.isNull(routeName)) {
			sql = sql + " and name like '%"+routeName+"%'";
		} 
		if(groupId > 0) {
			sql = sql + " and routeGroupId=" + groupId;
		} 
		return this.jdbcTemplate.queryForInt(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RouteBean> getAll(int start, int limit, String routeName, int groupId) {
		String sql = "select * from route where 1=1";
		if(!ValidateUtils.isNull(routeName)) {
			sql = sql + " and name like '%"+routeName+"%'";
		} 
		if(groupId > 0) {
			sql = sql + " and routeGroupId=" + groupId;
		} 
		sql = sql + " order by routeGroupId, level limit " + start + ", " + limit;
		return this.jdbcTemplate.query(sql, new RouteRowMapper());
	}

	@Override
	public int getCountOfWithoutDisabled() {
		String sql = "select count(*) from route where available=1";
		return this.jdbcTemplate.queryForInt(sql);
	}

	@Override
	public int getCountOfDisabled() {
		String sql = "select count(*) from route where available=0";
		return this.jdbcTemplate.queryForInt(sql);
	}

	@Override
	public int getCount(int ownerId) {
		// TODO Auto-generated method stub
		return 0;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RouteBean> getAllByGroup(int groupId) {
		String sql = "select * from route where routeGroupId=" + groupId + " order by level desc";
		return this.jdbcTemplate.query(sql, new RouteRowMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RouteBean> getAllByScenic(int scenicId) {
		String sql = "select route.* from route, route_scenic where route_scenic.scenicId=" + scenicId + 
		" and route.id=route_scenic.routeId and route.available=1 order by route.level desc";
		return this.jdbcTemplate.query(sql, new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				RouteBean route = new RouteBean();
				route.setAvailable(true);
				route.setContents(rs.getString("route.contents"));
				route.setId(rs.getInt("route.id"));
				route.setLevel(rs.getInt("route.level"));
				route.setName(rs.getString("route.name"));
				route.setPrice(rs.getString("route.price"));
				route.setRouteGroupId(rs.getInt("route.routeGroupId"));
				return route;
			}});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RouteBean> search(String keyWords) {
		String sql = "select * from route where name like '%" + keyWords.trim() + "%' and  available=1 order by level";
		System.out.println(sql);
		return this.jdbcTemplate.query(sql, new RouteRowMapper());
	}

}
